﻿using System;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using CnSharp.Database.Core;
using CnSharp.Database.MsSql;
using CnSharp.Database.Objects;
using Microsoft.SqlServer.Management.UI.VSIntegration;
using Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer;
using CnSharp.SSMS.AddIn.Util;

namespace CnSharp.SSMS.Demo.Controls
{
    public partial class FindControl : UserControl
    {

        private DbObjectContext _dbNode;
        private string _database;
        private CnSharp.Database.Objects.Database _db;

        public FindControl()
        {
            InitializeComponent();
        }

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            ActiveControl = txtKeywords.Control;

            UpdateObjectExplorerInfo();
            BindingDbs();


            var objectExplorer =
                (INavigationContextProvider)
                ServiceCache.ServiceProvider.GetService(typeof (INavigationContextProvider));
            objectExplorer.CurrentContextChanged += ProviderOnCurrentContextChanged;

            foreach (Control control in gbFilter.Controls)
            {
                if (control is CheckBox)
                {
                    var chb = control as CheckBox;
                    chb.CheckedChanged += chb_CheckedChanged;
                }
            }

            gbFilter.Visible = false;
        }

        private void ProviderOnCurrentContextChanged(object sender, NodesChangedEventArgs args)
        {
            UpdateObjectExplorerInfo();
        }

        private void chb_CheckedChanged(object sender, EventArgs e)
        {
            var chb = sender as CheckBox;
            if (chb.Checked)
                return;
            var allUnchecked = gbFilter.Controls.OfType<CheckBox>().All(box => !box.Checked);
            if (allUnchecked)
            {
                Common.ShowWarningMessageBox("At least one option required.");
                chb.Checked = true;
            }
        }

        protected override void OnVisibleChanged(EventArgs e)
        {
            base.OnVisibleChanged(e);

            if (Visible)
                txtKeywords.Select();
        }

        private void Provider_SelectionChanged(object sender, NodesChangedEventArgs args)
        {
            UpdateObjectExplorerInfo();
        }

        private void UpdateObjectExplorerInfo()
        {
            _dbNode = ObjectExplorerHelper.GetContext();
        }

        private void BindingDbs()
        {
            var db = new MsSql(_dbNode.ConnectionString);
            var dbs = db.GetDatabases().ToList();
            dbBox.Items.Clear();
            var i = 0;
            var selectedIndex = 0;
            dbs.ForEach(m =>
                {
                    dbBox.Items.Add(m);
                    if (m == _dbNode.Database)
                        selectedIndex = i;
                    i++;
                });
            dbBox.SelectedIndex = selectedIndex;
        } 


        private void toolStripButtonSearch_Click(object sender, EventArgs e)
        {
            listViewResults.Items.Clear();
            toolStripStatusLabelResults.Text = "0";

            _database = dbBox.Text.Trim();

            if (string.IsNullOrEmpty(_database))
            {
                Common.ShowErrorMessageBox("Select a database in the Object Explorer first.");
                return;
            }

            var dbServer = new MsSql(_dbNode.ConnectionString);

            //var dbs = dbServer.GetDatabases();
            //var dbList = new List<Database>();

            //dir = Path.Combine(dir, server);
            //if (!Directory.Exists(dir))
            //    Directory.CreateDirectory(dir);

            //foreach (var dbName in dbs)
            //{
            //    dbServer.ChangeDB(dbName);
            var db = new CnSharp.Database.Objects.Database();
            db.Tables = dbServer.GetTables(TableItemOptions.Column);
            db.Views = dbServer.GetViews();
            db.Procedures = dbServer.GetProcedures();
            db.Functions = dbServer.GetFunctions();


            var keywords = txtKeywords.Text.ToLower();

            if (chbTable.Checked)
            {
                var tables = db.Tables.Where(m => m.Name.ToLower().Contains(keywords));
                foreach (var table in tables)
                {
                    var item = new ResultItem
                        {
                            Text = table.Name,
                            Type = ShortObjectTypes.U.ToString()
                        };
                    listViewResults.Items.Add(item);
                }
            }

            if (chbView.Checked)
            {
                var views = db.Views.Where(m => m.Name.ToLower().Contains(keywords));
                foreach (var view in views)
                {
                    var item = new ResultItem
                        {
                            Text = view.Name,
                            Type = ShortObjectTypes.V.ToString()
                        };
                    listViewResults.Items.Add(item);
                }
            }

            if (chbSP.Checked)
            {
                var sps = db.Procedures.Where(m => m.Name.ToLower().Contains(keywords));
                foreach (var sp in sps)
                {
                    var item = new ResultItem
                        {
                            Text = sp.Name,
                            Type = ShortObjectTypes.P.ToString()
                        };
                    listViewResults.Items.Add(item);
                }
            }

            toolStripStatusLabelResults.Text = listViewResults.Items.Count.ToString();
        }

        #region real time search

        //private IEnumerable<ResultItem> SearchRealTimeItems()
        //{
        //     var command = new SqlCommand(string.Format(FindObjectSql, _database, GetFilter()));
        //        command.Parameters.AddWithValue("@search", "%" + txtKeywords.Text + "%");

        //        using (var connection = new SqlConnection(_dbNode.ConnectionString + ";Database=" + _database))
        //{
        //    command.Connection = connection;
        //    connection.Open();
        //    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
        //    {
        //        while (reader.Read())
        //        {
        //            var item = new ResultItem
        //                {
        //                    Id = reader.GetInt32(0),
        //                    Text = reader.GetString(1),
        //                    Type = reader.GetString(2).TrimEnd(),
        //                    Schema = reader.GetString(3),
        //                    Definition = reader[4].ToString()
        //                };
        //            yield return item;
        //        }
        //    }
        //}
        //}

 
        //private string GetFilter()
        //{
        //    var enums = new List<ShortObjectTypes>();
        //    if (chbTable.Checked)
        //        enums.Add(ShortObjectTypes.U);
        //    if (chbView.Checked)
        //        enums.Add(ShortObjectTypes.V);
        //    if (chbSP.Checked)
        //        enums.Add(ShortObjectTypes.P);
        //    if (chbFunc.Checked)
        //    {
        //        enums.Add(ShortObjectTypes.FN);
        //        enums.Add(ShortObjectTypes.TF);
        //    }
        //    var ids = from e in enums select "'" + e + "'";
        //    return string.Join(",", ids.ToArray());
        //}


        //        const string FindObjectSql =
        //            @"SELECT id, name, xtype,schemaName,definition
        //from (
        // SELECT a.id, a.name, a.xtype,c.definition,b.name schemaName,  
        // (case a.xtype 
        //when 'U' then 1
        //when 'V' then 2
        //when 'P' then 3
        //when 'FN' then 4
        //when 'TF' then 5
        //else 99
        //end  
        //) as SortNo
        //FROM  {0}.sys.sysobjects  a
        //join {0}.sys.sysusers  b on  a.uid=b.uid
        //left outer     JOIN  {0}.sys.sql_modules  c
        //ON  a.id = c.object_id 
        //WHERE (c.definition LIKE @search or a.name  LIKE @search)
        //and a.xtype in ({1})
        //) t 
        //ORDER BY SortNo
        // ";
        #endregion

        private void listViewResults_DoubleClick(object sender, EventArgs e)
        {
            if (listViewResults.SelectedItems.Count == 1)
            {
                // load definition
                var item = (ResultItem) listViewResults.SelectedItems[0];

                if (item.Type == "U")
                {
                    //todo show table schema
                }
                else
                {
                    OpenScriptDocument(item);
                }

                BeginInvoke(new Action(() =>
                    {
                        var itemType = (ShortObjectTypes) Enum.Parse(typeof (ShortObjectTypes), item.Type);
                        var objType = itemType.ToSqlObjectType();
                        ObjectExplorerHelper.LocateObject(objType, item.Text);
                    })
                    );

            }
        }

        private void OpenScriptDocument(ResultItem item)
        {

            var db = new MsSql(_dbNode.ConnectionString);
            item.Definition = db.GetObjectScript(item.Text, item.ShortObjectType.ToDbObjectType());

            var regex = new Regex(Regex.Escape("CREATE"));
            var definition = regex.Replace(item.Definition, "ALTER", 1);

            DocumentHelper.NewScriptDocument(definition);
        }


        private void toolStripTextBox_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                // Begin search
                toolStripButtonSearch_Click(null, EventArgs.Empty);
            }
        }

        private void listViewResults_SizeChanged(object sender, EventArgs e)
        {
            listViewResults.Columns[0].Width = listViewResults.ClientSize.Width;
        }

        private void tsbFilter_Click(object sender, EventArgs e)
        {
            gbFilter.Visible = !gbFilter.Visible;
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            BindingDbs();
        }

    }

    public enum ShortObjectTypes
    {
        U,
        V,
        P,
        FN,
        TF
    }

    public static class ObjectTypeMapper
    {
        public static string ToSqlObjectType(this ShortObjectTypes shortObjectTypes)
        {
            if (shortObjectTypes == ShortObjectTypes.U)
                return "Table";
            if (shortObjectTypes == ShortObjectTypes.V)
                return "View";
            if (shortObjectTypes == ShortObjectTypes.P)
                return "StoredProcedure";
            if (shortObjectTypes == ShortObjectTypes.TF || shortObjectTypes == ShortObjectTypes.FN)
                return "UserDefinedFunction";
            throw new NotSupportedException();
        }

        public static DbObjectType ToDbObjectType(this ShortObjectTypes shortObjectTypes)
        {
            if (shortObjectTypes == ShortObjectTypes.U)
                return DbObjectType.Table;
            if (shortObjectTypes == ShortObjectTypes.V)
                return DbObjectType.View;
            if (shortObjectTypes == ShortObjectTypes.P)
                return DbObjectType.Procedure;
            if (shortObjectTypes == ShortObjectTypes.TF || shortObjectTypes == ShortObjectTypes.FN)
                return DbObjectType.Function;
            throw new NotSupportedException();
        }
    }
}